package com.game.astd.web;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;

public class DBUtil
{
	static
	{
		try
		{
			PropertyConfigurator.configure(DBUtil.class.getClassLoader().getResource("./proxool.property").getPath());
		} catch (ProxoolException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * The main method for test.
	 */
	public static List<Map<String, String>> executeQuery(String sql, List<String> paramList)
	{
		List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
		Connection con = null;
		try
		{
			con = DriverManager.getConnection("proxool.property-test");
			PreparedStatement psta = con.prepareStatement(sql);
			for (int i = 0; i < paramList.size(); i++)
			{
				psta.setString(1, paramList.get(i));
			}
			ResultSet rs = psta.executeQuery();
			ResultSetMetaData rsm = rs.getMetaData();
			int count = rsm.getColumnCount();
			while (rs.next())
			{
				Map<String, String> dMap = new HashMap<String, String>();
				for (int i = 1; i <= count; i++)
				{
					String columnName = rsm.getColumnName(i);
					String v = rs.getString(columnName);
					dMap.put(columnName, v);

				}
				resultList.add(dMap);
			}
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			if (con != null)
			{
				try
				{
					con.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return resultList;
	}

	/**
	 * The main method for test.
	 */
	public static List<Map<String, String>> executeQuery(String sql, String[] paramList)
	{
		List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
		Connection con = null;
		try
		{
			con = DriverManager.getConnection("proxool.property-test");
			PreparedStatement psta = con.prepareStatement(sql);
			for (int i = 0; i < paramList.length; i++)
			{
				psta.setString(1, paramList[i]);
			}
			ResultSet rs = psta.executeQuery();
			ResultSetMetaData rsm = rs.getMetaData();
			int count = rsm.getColumnCount();
			while (rs.next())
			{
				Map<String, String> dMap = new HashMap<String, String>();
				for (int i = 1; i <= count; i++)
				{
					String columnName = rsm.getColumnName(i);
					String v = rs.getString(columnName);
					dMap.put(columnName, v);

				}
				resultList.add(dMap);
			}
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			if (con != null)
			{
				try
				{
					con.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return resultList;
	}

	/**
	 * 
	 * @param sql
	 * @return
	 */
	public static int executeUpdate(String sql, List<String> paramList)
	{
		int size = 0;
		Connection con = null;
		try
		{
			con = DriverManager.getConnection("proxool.property-test");
			PreparedStatement psta = con.prepareStatement(sql);
			for (int i = 1; i <= paramList.size(); i++)
			{
				psta.setString(i, paramList.get(i - 1));
			}
			size = psta.executeUpdate();
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			if (con != null)
			{
				try
				{
					con.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		return size;
	}

	/**
	 * 
	 * @param sql
	 * @return
	 */
	public static int executeUpdate(String sql, String[] paramList)
	{
		int size = 0;
		Connection con = null;
		try
		{
			con = DriverManager.getConnection("proxool.property-test");
			PreparedStatement psta = con.prepareStatement(sql);
			for (int i = 1; i <= paramList.length; i++)
			{
				System.out.println(paramList[i - 1]);
				psta.setString(i, paramList[i - 1]);
			}
			size = psta.executeUpdate();
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			if (con != null)
			{
				try
				{
					con.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		return size;
	}
	
	/**
	 * 
	 * @param sql
	 * @return
	 */
	public static int executeUpdate(String sql)
	{
		int size = 0;
		Connection con = null;
		try
		{
			con = DriverManager.getConnection("proxool.property-test");
			PreparedStatement psta = con.prepareStatement(sql);
			size = psta.executeUpdate();
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			if (con != null)
			{
				try
				{
					con.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		return size;
	}
}
